/*
	This Procedure loads all the Running, Hold and Shipped Lots Inventory in Chiptrac.
 	EXEC sp_inventory_summary_report_proc 0,'0','0','0','0','0'
	lot_dropout = 0
	Need to activate the commented condition for Lot Category when lot category in lot table is not null
	SELECT DISTINCT PS_TYPE,PS_TYPE_NAME FROM PROCESS_STEP
SELECT * FROM LOT_TABLE_HISTORY ORDER BY TIMESTAMP DESC
*/

ALTER  PROC sp_inventory_summary_report_proc

	@PRODUCT_ID 	INT,
	@LOT_CATEGORY	VARCHAR(50),
	@VENDOR		VARCHAR(50),
	@STATUS		VARCHAR(50),
	@ASOFDATE	VARCHAR(50),
	@DEFAULT_PROMPT	VARCHAR(50)
AS
BEGIN

SET NOCOUNT ON
CREATE TABLE #INVENTORY_SUMMARY_TABLE ( FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),WAFER_QTY_IN INT,QTY_IN INT,
					--LOT_CATEGORY VARCHAR(50),VENDOR_NAME VARCHAR(50),
					PART_NUMBER VARCHAR(50), PROCESSSTEP_ID INT,BASE_PRODUCT VARCHAR(50),--PS_TYPE_ID INT,
					PS_TYPE VARCHAR(50),GROSS_DIE INT,NET_DIE INT, TRA_WAFER_QTY_IN INT,TRA_QTY_IN INT )

DECLARE @RESULTS TABLE ( [BASE PRODUCT] VARCHAR(50),[PART NUMBER] VARCHAR(50),
			[FAB QTY] INT, [WAFER STORE QTY] INT, [SORT QTY] INT, [DIE BANK QTY] INT,
			[ASSEMBLY QTY] INT,[PRE TEST] INT,[TEST FT] INT, [EOL QTY] INT,
			[FINISHED GOODS QTY] INT,[DIE TOTAL FOR WAFERS] INT,[FAB DIE QTY] INT,[SORT DIE QTY] INT,[WS DIE QTY] INT )

DECLARE @SQLSTRING 		NVARCHAR(4000)
DECLARE @SQLSTRING1 	NVARCHAR(3000)

DECLARE @BASE_PRODUCT		VARCHAR(50)
DECLARE @PART_NUMBER 		VARCHAR(50)
DECLARE @PS_TYPE 		VARCHAR(50)
DECLARE @PACKAGE_TYPE 		VARCHAR(50)
DECLARE @WAFER_QTY_IN 		INT
DECLARE @QTY_IN 		INT
DECLARE @TRA_WAFER_QTY_IN 	INT
DECLARE @TRA_QTY_IN 		INT

DECLARE @GROSS_DIE		INT
DECLARE @NET_DIE		INT
DECLARE @DIE_TOTAL_FOR_WAFERS	INT

DECLARE @TEMP_VARCHAR 	VARCHAR(50)
DECLARE @START_DATE 	DATETIME
DECLARE @END_DATE 	DATETIME

DECLARE @LOT_CURSOR CURSOR

	SET @SQLSTRING = N'INSERT INTO #INVENTORY_SUMMARY_TABLE
		SELECT  FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, QTY_IN,--LOT_CATEGORY,L_VENDOR_NAME, 
		PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT,PS_TYPE,
		( CASE 
			WHEN PS_TYPE = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE = ''SORT'' THEN convert(int,cast( ( select Parameter_value from Process_Parameter where Parameter_Key = ''Gross Die/Wafer'' AND ProcessStep_id = ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID =  ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID ) ) ) as float))
			WHEN PS_TYPE = ''WAFER BANK'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PP.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Gross Die/Wafer'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ), 
		( CASE 
			WHEN PS_TYPE = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Plan NDW'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE = ''SORT'' THEN convert(int,cast( ( select Parameter_value from Process_Parameter where Parameter_Key = ''Plan NDW'' AND ProcessStep_id = ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID =  ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID ) ) ) as float))
			WHEN PS_TYPE = ''WAFER BANK'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PP.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Plan NDW'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ),
		NULL AS TRA_WAFER_QTY_IN , NULL AS TRA_QTY_IN '
	
	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE_HISTORY LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '

		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	END
		SET @SQLSTRING = @SQLSTRING + ' WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		--AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )
		AND (P_TYPE IS NULL OR P_TYPE = ''BOM'')
		AND P.OBSOLETE != 0 '

	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE lth.lot_id = lt.lot_id and LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END

	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID = ' + convert(varchar,@PRODUCT_ID) 
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(LT.LOT_CATEGORY, ''PROD'') =  ''' +  @LOT_CATEGORY + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END 
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.L_VENDOR_NAME =  ''' +  @VENDOR + '''' 
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CURRENT_STATUS =  ''' +  @STATUS + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )' 
	END	
	
	SET @SQLSTRING1 = N' UNION
	SELECT FAB_LOT_NUMBER, VENDOR_LOT_NUMBER,NULL,NULL,--LOT_CATEGORY,L_VENDOR_NAME,
		 PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT, PS_TYPE,
		( CASE 
			WHEN PS_TYPE = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE = ''SORT'' THEN convert(int,cast( ( select Parameter_value from Process_Parameter where Parameter_Key = ''Gross Die/Wafer'' AND ProcessStep_id = ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID =  ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID ) ) ) as float))
			WHEN PS_TYPE = ''WAFER BANK'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PP.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Gross Die/Wafer'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ),
		( CASE 
			WHEN PS_TYPE = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Plan NDW'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE = ''SORT'' THEN convert(int,cast( ( select Parameter_value from Process_Parameter where Parameter_Key = ''Plan NDW'' AND ProcessStep_id = ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID =  ( SELECT PROCESSSTEP_ID  FROM Process_Step_Traverse_Table WHERE NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID ) ) ) as float))
			WHEN PS_TYPE = ''WAFER BANK'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PP.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Plan NDW'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ),
		(CASE 
			WHEN ( PS_TYPE_NAME = ''FAB'' OR PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''BUMPING'' OR PS_TYPE_NAME = ''SORT'' ) THEN SHIP_QTY 
			ELSE NULL
		END 
		)AS TRA_WAFER_QTY_IN, 
		(CASE 
			WHEN ( PS_TYPE_NAME = ''ASSEMBLY'' OR PS_TYPE_NAME = ''DIE BANK'' OR PS_TYPE_NAME = ''Class Store'' OR PS_TYPE_NAME = ''TEST'' ) THEN SHIP_QTY 
			ELSE NULL
		END 
		)AS TRA_QTY_IN '

	IF @ASOFDATE IS NOT NULL AND @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' FROM 
			SHIPPED_LOT SL, LOT_TABLE LT,LOCATION L, PRODUCT P, PROCESS_STEP PS '
	END
	ELSE
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' FROM 
			SHIPPED_LOT_HISTORY SL, LOT_TABLE LT,LOCATION L, PRODUCT P, PROCESS_STEP PS '
	END

	SET @SQLSTRING1 = @SQLSTRING1 + ' WHERE 
		SL.LOT_ID = LT.LOT_ID
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND SL.STATUS = ''TRANSIT''
		AND (P_TYPE IS NULL OR P_TYPE = ''BOM'')
		AND P.OBSOLETE != 0 '
	
	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SL.TimeStamp BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END
	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + 'AND P.P_PARENT_PRODUCT_ID =  '+  convert(varchar,@PRODUCT_ID)
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND L.L_VENDOR_NAME =  ''' +  @VENDOR + '''' 
	END 
		
	--PRINT @SQLSTRING
	--PRINT @SQLSTRING1	

	EXEC(@SQLSTRING + @SQLSTRING1)
	
	INSERT INTO @RESULTS ( [BASE PRODUCT],[PART NUMBER] ) (SELECT DISTINCT BASE_PRODUCT,PART_NUMBER FROM #INVENTORY_SUMMARY_TABLE )

	SET @LOT_CURSOR = CURSOR FOR 

			SELECT 
				SUM(WAFER_QTY_IN),SUM(QTY_IN),PART_NUMBER,BASE_PRODUCT,PS_TYPE,GROSS_DIE,NET_DIE,SUM(TRA_WAFER_QTY_IN),SUM(TRA_QTY_IN)
			from 
				#INVENTORY_SUMMARY_TABLE
			GROUP BY 
				BASE_PRODUCT,PART_NUMBER,PS_TYPE,GROSS_DIE,NET_DIE

	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN,@QTY_IN,@PART_NUMBER,@BASE_PRODUCT,@PS_TYPE,@GROSS_DIE,@NET_DIE, @TRA_WAFER_QTY_IN,@TRA_QTY_IN
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @NET_DIE IS NULL OR @NET_DIE = ''
		BEGIN
				SET @NET_DIE = @GROSS_DIE
		END
		IF ( @PS_TYPE = 'FAB')
		BEGIN
						
			SET @DIE_TOTAL_FOR_WAFERS = ( (ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0)) * ISNULL(@NET_DIE,0) )
							
			UPDATE @RESULTS SET [FAB QTY] = (ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0) ),[FAB DIE QTY] = @DIE_TOTAL_FOR_WAFERS
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Wafer Bank')
		BEGIN
			
			SET @DIE_TOTAL_FOR_WAFERS = ( ( ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0) ) * ISNULL(@NET_DIE,0) )
					
			UPDATE @RESULTS SET [WAFER STORE QTY] = ( ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0) ),[WS DIE QTY] = @DIE_TOTAL_FOR_WAFERS
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'SORT')
		BEGIN
			
			SET @DIE_TOTAL_FOR_WAFERS = ( ( ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0) ) * ISNULL(@NET_DIE,0) )
							
			UPDATE @RESULTS SET [SORT QTY] = ( ISNULL(@WAFER_QTY_IN,0) + ISNULL(@TRA_WAFER_QTY_IN, 0) ),[SORT DIE QTY] = @DIE_TOTAL_FOR_WAFERS
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Die Bank')
		BEGIN
						
			UPDATE @RESULTS SET [DIE BANK QTY] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Assembly')
		BEGIN
			
			UPDATE @RESULTS SET [ASSEMBLY QTY] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'PRE TEST')
		BEGIN
			
			UPDATE @RESULTS SET [PRE TEST] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'FT')
		BEGIN
			
			UPDATE @RESULTS SET [TEST FT] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
			
		END
		ELSE IF ( @PS_TYPE = 'EOL')
		BEGIN
			
			UPDATE @RESULTS SET [EOL QTY] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
			
		END
		/*ELSE IF ( @PS_TYPE = 'Tape & Reel')
		BEGIN
						
			UPDATE @RESULTS SET [TAPE REEL QTY] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END*/
		ELSE IF ( @PS_TYPE = 'FG')
		BEGIN
						
			UPDATE @RESULTS SET [FINISHED GOODS QTY] = ( ISNULL(@QTY_IN,0) + ISNULL(@TRA_QTY_IN, 0) )
			WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
		END
				
			SET @BASE_PRODUCT	= NULL
			SET @PART_NUMBER 	= NULL
			SET @WAFER_QTY_IN 	= NULL
			SET @QTY_IN 		= NULL
			SET @PS_TYPE 		= NULL
			SET @TRA_WAFER_QTY_IN = NULL
			SET @TRA_QTY_IN		= NULL
			SET @NET_DIE		= NULL
			SET @GROSS_DIE		= NULL
		FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN,@QTY_IN,@PART_NUMBER,@BASE_PRODUCT,@PS_TYPE,@GROSS_DIE,@NET_DIE,@TRA_WAFER_QTY_IN,@TRA_QTY_IN
	END
 	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR

	SELECT 
		[BASE PRODUCT],[PART NUMBER],[FAB QTY],[WAFER STORE QTY],[SORT QTY],( ISNULL([FAB DIE QTY],0)+ISNULL([SORT DIE QTY],0)+ISNULL([WS DIE QTY],0) ) as [EXPECTED DIE PER WAFER],
		[DIE BANK QTY],[ASSEMBLY QTY],[PRE TEST],[TEST FT],[EOL QTY],/*[TAPE REEL QTY],*/[FINISHED GOODS QTY] AS [FG QTY],
--		(ISNULL([DIE BANK QTY],0)+ISNULL([ASSEMBLY QTY],0)+ISNULL([PRE TEST],0)+ISNULL([EOL Qty],0)+
--		 ISNULL([TEST FT],0)+ISNULL([TAPE REEL QTY],0)+ISNULL([FINISHED GOODS QTY],0) ) AS [TOTAL DIE QTY],
		( ISNULL([FAB DIE QTY],0)+ISNULL([SORT DIE QTY],0)+ISNULL([WS DIE QTY],0) + ISNULL([DIE BANK QTY],0)
		  +ISNULL([ASSEMBLY QTY],0)+ISNULL([PRE TEST],0)+ISNULL([EOL Qty],0)+ ISNULL([TEST FT],0)
		  /*+ISNULL([TAPE REEL QTY],0)*/+ISNULL([FINISHED GOODS QTY],0) ) AS [TOTAL QTY]
	FROM @RESULTS 
	ORDER BY [BASE PRODUCT],[PART NUMBER]
END
